"""地址管理 CRUD 操作"""
from typing import Optional, Tuple, List, Dict, Any

from app.models.address_models import BaseAddress, DeviceAddress
from app.schemas.address_schemas import (
	AddressCreate, AddressUpdate, DeviceAddressCreate, DeviceAddressUpdate
)
from common.database import DatabaseService
from common.exceptions import BizException, ErrorCode
from common.logger import create_logger

logger = create_logger("address_crud")


# ============ 地址管理 ============

async def get_base_address_list(db: DatabaseService, query, page: int, size: int) -> Tuple[int, List[Dict[str, Any]]]:
	"""获取基础地址列表"""
	where_conditions = ["is_active = 1"]
	params = []

	if query.keyword:
		where_conditions.append("(name LIKE %s OR full_name LIKE %s)")
		keyword_param = f"%{query.keyword}%"
		params.extend([keyword_param, keyword_param])

	if query.level is not None:
		where_conditions.append("level = %s")
		params.append(query.level)

	if query.parent_id is not None:
		where_conditions.append("parent_id = %s")
		params.append(query.parent_id)

	if query.is_active is not None:
		where_conditions[-1] = "is_active = %s"  # 替换默认的 is_active = 1
		params.append(1 if query.is_active else 0)

	where_clause = " AND ".join(where_conditions)

	# 获取总数
	count_sql = f"SELECT COUNT(*) as total FROM base_region_address WHERE {where_clause}"
	total_result = db.get_one(count_sql, params)
	total = total_result["total"] if total_result else 0

	# 获取分页数据
	offset = (page - 1) * size
	list_sql = f"""
        SELECT id, parent_id, name, full_name, level, region_code, longitude, latitude, 
               source, is_active, created_at, updated_at
        FROM base_region_address 
        WHERE {where_clause}
        ORDER BY level ASC, id ASC
        LIMIT %s OFFSET %s
    """
	params.extend([size, offset])
	results = db.get_all(list_sql, params)

	# 解析region_code并获取对应的省市区信息
	await _enrich_address_list_with_region_info(db, results)

	return total, results


async def get_base_address_by_id(db: DatabaseService, address_id: int) -> Optional[Dict[str, Any]]:
	"""根据ID获取基础地址"""
	result = await get_address_by_id(db, address_id)
	if not result:
		return None

	address_dict = result.model_dump()

	# 解析region_code并获取对应的省市区信息
	await _enrich_address_list_with_region_info(db, [address_dict])

	return address_dict


async def create_base_address(db: DatabaseService, payload: AddressCreate) -> int:
	"""创建基础地址"""
	return await create_address(db, payload)


async def update_base_address(db: DatabaseService, address_id: int, payload: AddressUpdate) -> bool:
	"""更新基础地址"""
	return await update_address(db, address_id, payload)


async def delete_base_address(db: DatabaseService, address_id: int) -> bool:
	"""删除基础地址（逻辑删除）"""
	sql = "UPDATE base_region_address SET is_active = 0, updated_at = CURRENT_TIMESTAMP WHERE id = %s"
	try:
		return db.update(sql, (address_id,)) > 0
	except Exception as e:
		logger.exception("删除基础地址失败，address_id=%s", address_id)
		raise BizException.from_error_code(ErrorCode.INTERNAL_SERVER_ERROR, message="删除基础地址失败") from e


async def get_address_by_id(db: DatabaseService, address_id: int) -> Optional[BaseAddress]:
	"""根据ID获取地址"""
	sql = """
        SELECT id, parent_id, name, full_name, level, region_code, longitude, latitude, 
               source, is_active, created_at, updated_at
        FROM base_region_address
        WHERE id = %s AND is_active = 1
    """
	result = db.get_one(sql, (address_id,))
	return BaseAddress.model_validate(result) if result else None


async def create_address(db: DatabaseService, payload: AddressCreate) -> int:
	"""创建地址"""
	sql = """
        INSERT INTO base_region_address (parent_id, name, full_name, level, region_code, 
                                 longitude, latitude, source, is_active)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
    """
	try:
		return db.insert(sql, (
			payload.parent_id, payload.name, payload.full_name, payload.level,
			payload.region_code, payload.longitude, payload.latitude,
			payload.source, payload.is_active
		))
	except Exception as e:
		logger.exception("创建地址失败，payload=%s", payload)
		raise BizException.from_error_code(ErrorCode.INTERNAL_SERVER_ERROR, message="创建地址失败") from e


async def update_address(db: DatabaseService, address_id: int, payload: AddressUpdate) -> bool:
	"""更新地址"""
	fields, params = [], []
	for field, value in payload.model_dump(exclude_unset=True).items():
		fields.append(f"{field} = %s")
		params.append(value)

	if not fields:
		return True

	params.append(address_id)
	sql = f"UPDATE base_region_address SET {', '.join(fields)}, updated_at = CURRENT_TIMESTAMP WHERE id = %s"

	try:
		return db.update(sql, params) > 0
	except Exception as e:
		logger.exception("更新地址失败，address_id=%s, payload=%s", address_id, payload)
		raise BizException.from_error_code(ErrorCode.INTERNAL_SERVER_ERROR, message="更新地址失败") from e


# ============ 设备地址关联 ============

async def get_addresses_by_region_prefix_paged(
		db: DatabaseService,
		region_prefix: str,
		min_level: int = 4,
		keyword: Optional[str] = None,
		page: int = 1,
		size: int = 20,
) -> Tuple[int, List[Dict[str, Any]]]:
	"""
	根据 region_code 前缀分页查询地址（level>=min_level）
	返回 (total, [dict, ...])，dict 字段与 base_region_address 完全一致
	"""
	where = [
		"region_code LIKE %s",
		"level >= %s",
		"is_active = 1",
	]
	params: List[Any] = [f"{region_prefix}%", min_level]

	if keyword:
		where.append("(name LIKE %s OR full_name LIKE %s)")
		kw = f"%{keyword}%"
		params.extend([kw, kw])

	where_clause = " AND ".join(where)

	# 总数
	count_sql = f"SELECT COUNT(*) AS total FROM base_region_address WHERE {where_clause}"
	total_row = db.get_one(count_sql, params)
	total = total_row["total"] if total_row else 0
	if total == 0:
		return 0, []

	# 分页数据
	offset = (page - 1) * size
	list_sql = f"""
        SELECT id, parent_id, name, full_name, level, region_code,
               longitude, latitude, source, is_active, created_at, updated_at
        FROM base_region_address
        WHERE {where_clause}
        ORDER BY level ASC, id ASC
        LIMIT %s OFFSET %s
    """
	params.extend([size, offset])
	rows = db.get_all(list_sql, params)
	return total, [dict(r) for r in rows]


async def get_device_address_list(db: DatabaseService, query, page: int, size: int) -> Tuple[int, List[Dict[str, Any]]]:
	"""获取设备地址关联列表（包含地址详细信息）"""
	where_conditions = ["1=1"]
	params = []

	if query.device_type:
		where_conditions.append("da.device_type = %s")
		params.append(query.device_type)

	if query.device_id is not None:
		where_conditions.append("da.device_id = %s")
		params.append(query.device_id)

	if query.address_type:
		where_conditions.append("da.address_type = %s")
		params.append(query.address_type)

	if query.is_primary is not None:
		where_conditions.append("da.is_primary = %s")
		params.append(1 if query.is_primary else 0)

	where_clause = " AND ".join(where_conditions)

	# 获取总数
	count_sql = f"""
        SELECT COUNT(*) as total 
        FROM dev_device_address da
        LEFT JOIN base_region_address ba ON da.address_id = ba.id
        WHERE {where_clause}
    """
	total_result = db.get_one(count_sql, params)
	total = total_result["total"] if total_result else 0

	# 获取分页数据（连表查询获取地址详细信息）
	offset = (page - 1) * size
	list_sql = f"""
        SELECT 
            da.id, da.device_type, da.device_id, da.address_id, da.address_type, 
            da.is_primary, da.description, da.created_at,
            ba.name as address_name, ba.full_name as full_address, 
            ba.longitude, ba.latitude
        FROM dev_device_address da
        LEFT JOIN base_region_address ba ON da.address_id = ba.id
        WHERE {where_clause}
        ORDER BY da.device_type, da.device_id, da.is_primary DESC, da.created_at ASC
        LIMIT %s OFFSET %s
    """
	params.extend([size, offset])
	results = db.get_all(list_sql, params)

	return total, results


async def get_device_address_by_id(db: DatabaseService, device_address_id: int) -> Optional[Dict[str, Any]]:
	"""根据ID获取设备地址关联（包含地址详细信息）"""
	sql = """
        SELECT 
            da.id, da.device_type, da.device_id, da.address_id, da.address_type, 
            da.is_primary, da.description, da.created_at,
            ba.name as address_name, ba.full_name as full_address, 
            ba.longitude, ba.latitude
        FROM dev_device_address da
        LEFT JOIN base_region_address ba ON da.address_id = ba.id
        WHERE da.id = %s
    """
	result = db.get_one(sql, (device_address_id,))
	return result


async def update_device_address(db: DatabaseService, device_address_id: int, payload: DeviceAddressUpdate) -> bool:
	"""根据ID更新设备地址关联"""
	fields, params = [], []
	for field, value in payload.model_dump(exclude_unset=True).items():
		fields.append(f"{field} = %s")
		params.append(value)

	if not fields:
		return True

	params.append(device_address_id)
	sql = f"UPDATE dev_device_address SET {', '.join(fields)} WHERE id = %s"

	try:
		return db.update(sql, params) > 0
	except Exception as e:
		logger.exception("更新设备地址关联失败，device_address_id=%s, payload=%s", device_address_id, payload)
		raise BizException.from_error_code(ErrorCode.INTERNAL_SERVER_ERROR, message="更新设备地址关联失败") from e


async def delete_device_address(db: DatabaseService, device_address_id: int) -> bool:
	"""根据ID删除设备地址关联"""
	sql = "DELETE FROM dev_device_address WHERE id = %s"
	try:
		return db.delete(sql, (device_address_id,)) > 0
	except Exception as e:
		logger.exception("删除设备地址关联失败，device_address_id=%s", device_address_id)
		raise BizException.from_error_code(ErrorCode.INTERNAL_SERVER_ERROR, message="删除设备地址关联失败") from e


async def get_device_address(db: DatabaseService, device_type: str, device_id: int,
							 address_type: str = "INSTALLATION") -> Optional[DeviceAddress]:
	"""获取设备地址关联"""
	sql = """
        SELECT id, device_type, device_id, address_id, address_type, is_primary, description, created_at
        FROM dev_device_address
        WHERE device_type = %s AND device_id = %s AND address_type = %s
    """
	result = db.get_one(sql, (device_type, device_id, address_type))
	return DeviceAddress.model_validate(result) if result else None


async def get_device_addresses(db: DatabaseService, device_type: str, device_id: int) -> List[DeviceAddress]:
	"""获取设备所有地址关联"""
	sql = """
        SELECT id, device_type, device_id, address_id, address_type, is_primary, description, created_at
        FROM dev_device_address
        WHERE device_type = %s AND device_id = %s
        ORDER BY is_primary DESC, created_at ASC
    """
	results = db.get_all(sql, (device_type, device_id))
	return [DeviceAddress.model_validate(result) for result in results]


async def create_device_address(db: DatabaseService, payload: DeviceAddressCreate) -> int:
	"""创建设备地址关联"""
	# 如果设置为主地址，先将其他地址设为非主地址
	if payload.is_primary:
		await _set_other_addresses_non_primary(db, payload.device_type, payload.device_id, payload.address_type)

	sql = """
        INSERT INTO dev_device_address (device_type, device_id, address_id, address_type, 
                                       is_primary, description)
        VALUES (%s, %s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE
        address_id = VALUES(address_id),
        is_primary = VALUES(is_primary),
        description = VALUES(description)
    """
	try:
		return db.insert(sql, (
			payload.device_type, payload.device_id, payload.address_id,
			payload.address_type, payload.is_primary, payload.description
		))
	except Exception as e:
		logger.exception("创建设备地址关联失败，payload=%s", payload)
		raise BizException.from_error_code(ErrorCode.INTERNAL_SERVER_ERROR, message="创建设备地址关联失败") from e


async def update_device_address_by_key(db: DatabaseService, device_type: str, device_id: int,
									   address_type: str, payload: DeviceAddressUpdate) -> bool:
	"""根据设备信息更新设备地址关联"""
	# 如果设置为主地址，先将其他地址设为非主地址
	if payload.is_primary:
		await _set_other_addresses_non_primary(db, device_type, device_id, address_type)

	fields, params = [], []
	for field, value in payload.model_dump(exclude_unset=True).items():
		fields.append(f"{field} = %s")
		params.append(value)

	if not fields:
		return True

	params.extend([device_type, device_id, address_type])
	sql = f"""
        UPDATE dev_device_address 
        SET {', '.join(fields)}
        WHERE device_type = %s AND device_id = %s AND address_type = %s
    """

	try:
		return db.update(sql, params) > 0
	except Exception as e:
		logger.exception("更新设备地址关联失败，device_type=%s, device_id=%s, payload=%s",
						 device_type, device_id, payload)
		raise BizException.from_error_code(ErrorCode.INTERNAL_SERVER_ERROR, message="更新设备地址关联失败") from e


async def delete_device_address_by_key(db: DatabaseService, device_type: str, device_id: int,
									   address_type: str) -> bool:
	"""根据设备信息删除设备地址关联"""
	sql = "DELETE FROM dev_device_address WHERE device_type = %s AND device_id = %s AND address_type = %s"
	try:
		return db.delete(sql, (device_type, device_id, address_type)) > 0
	except Exception as e:
		logger.exception("删除设备地址关联失败，device_type=%s, device_id=%s, address_type=%s",
						 device_type, device_id, address_type)
		raise BizException.from_error_code(ErrorCode.INTERNAL_SERVER_ERROR, message="删除设备地址关联失败") from e


async def get_device_with_address_info(db: DatabaseService, device_type: str, device_id: int) -> dict:
	"""获取设备及其地址信息"""
	sql = """
        SELECT da.id as device_address_id, da.address_type, da.is_primary, da.description,
               ba.id as address_id, ba.name as address_name, ba.full_name, 
               ba.longitude, ba.latitude, ba.level, ba.region_code
        FROM dev_device_address da
        INNER JOIN base_region_address ba ON da.address_id = ba.id
        WHERE da.device_type = %s AND da.device_id = %s AND ba.is_active = 1
        ORDER BY da.is_primary DESC, da.created_at ASC
    """
	results = db.get_all(sql, (device_type, device_id))

	addresses = []
	for result in results:
		addresses.append({
			"device_address_id": result["device_address_id"],
			"address_type": result["address_type"],
			"is_primary": bool(result["is_primary"]),
			"description": result["description"],
			"address_id": result["address_id"],
			"address_name": result["address_name"],
			"full_address": result["full_name"],
			"longitude": result["longitude"],
			"latitude": result["latitude"],
			"level": result["level"],
			"region_code": result["region_code"]
		})

	return {
		"device_type": device_type,
		"device_id": device_id,
		"addresses": addresses
	}


# ============ 私有方法 ============

async def _enrich_address_list_with_region_info(db: DatabaseService, address_list: List[Dict[str, Any]]):
	"""使用递归CTE为地址列表添加省市名称和编码信息"""
	if not address_list:
		return

	# 收集所有需要查询的region_code
	region_codes = set()
	for address in address_list:
		region_code = address.get("region_code")
		if region_code:
			region_codes.add(region_code)

	if not region_codes:
		return

	# 使用递归CTE查询省市名称和编码
	# 这个CTE会从当前节点向上递归查找到省级和市级节点
	region_codes_list = list(region_codes)
	placeholders = ",".join(["%s"] * len(region_codes_list))

	recursive_cte_sql = f"""
	WITH RECURSIVE region_hierarchy AS (
		-- 基础查询：获取所有需要查询的地址节点
		SELECT 
			id,
			region_code,
			name,
			level,
			parent_id,
			region_code as original_code
		FROM base_region_address 
		WHERE region_code IN ({placeholders}) AND is_active = 1
		
		UNION ALL
		
		-- 递归查询：向上查找父级节点
		SELECT 
			p.id,
			p.region_code,
			p.name,
			p.level,
			p.parent_id,
			rh.original_code
		FROM base_region_address p
		INNER JOIN region_hierarchy rh ON p.id = rh.parent_id
		WHERE p.is_active = 1 AND p.level >= 2  -- 只查找到省级
	)
	SELECT 
		original_code,
		region_code,
		name,
		level
	FROM region_hierarchy
	WHERE level IN (2, 3)  -- 只要省级(2)和市级(3)
	ORDER BY original_code, level
	"""

	try:
		hierarchy_results = db.get_all(recursive_cte_sql, region_codes_list)

		# 构建省市名称和编码映射
		region_info = {}
		for row in hierarchy_results:
			original_code = row["original_code"]
			level = row["level"]
			name = row["name"]
			code = row["region_code"]

			if original_code not in region_info:
				region_info[original_code] = {}

			if level == 2:  # 省级
				region_info[original_code]["province_name"] = name
				region_info[original_code]["province_code"] = code
			elif level == 3:  # 市级
				region_info[original_code]["city_name"] = name
				region_info[original_code]["city_code"] = code

		# 为地址列表设置省市名称和编码
		for address in address_list:
			region_code = address.get("region_code")
			if region_code and region_code in region_info:
				info = region_info[region_code]
				address["province_name"] = info.get("province_name")
				address["province_code"] = info.get("province_code")
				address["city_name"] = info.get("city_name")
				address["city_code"] = info.get("city_code")

	except Exception as e:
		logger.warning(f"使用递归CTE查询省市信息失败，回退到物化路径方式: {e}")
		# 回退到使用物化路径(full_name)的方式
		await _enrich_address_list_with_materialized_path(db, address_list)


async def _enrich_address_list_with_materialized_path(db: DatabaseService, address_list: List[Dict[str, Any]]):
	"""使用物化路径(full_name)为地址列表添加省市名称和编码信息"""
	if not address_list:
		return

	# 收集所有需要查询的region_code
	region_codes = set()
	for address in address_list:
		region_code = address.get("region_code")
		if region_code:
			region_codes.add(region_code)

	if not region_codes:
		return

	# 使用region_code前缀进行匹配查询
	region_info = {}
	for region_code in region_codes:
		# 获取省级信息
		province_code = region_code[:2] + '0000'  # 省级编码格式
		province_sql = """
			SELECT name FROM base_region_address 
			WHERE region_code = %s AND level = 2 AND is_active = 1
		"""
		province_result = db.get_one(province_sql, (province_code,))
		province_name = province_result["name"] if province_result else None

		# 获取市级信息
		city_code = region_code[:4] + '00'  # 市级编码格式
		city_sql = """
			SELECT name FROM base_region_address 
			WHERE region_code = %s AND level = 3 AND is_active = 1
		"""
		city_result = db.get_one(city_sql, (city_code,))
		city_name = city_result["name"] if city_result else None

		region_info[region_code] = {
			"province_name": province_name,
			"province_code": province_code if province_name else None,
			"city_name": city_name,
			"city_code": city_code if city_name else None
		}

	# 为地址列表设置省市名称和编码
	for address in address_list:
		region_code = address.get("region_code")
		if region_code and region_code in region_info:
			info = region_info[region_code]
			address["province_name"] = info.get("province_name")
			address["province_code"] = info.get("province_code")
			address["city_name"] = info.get("city_name")
			address["city_code"] = info.get("city_code")


async def _set_other_addresses_non_primary(db: DatabaseService, device_type: str, device_id: int,
										   exclude_address_type: str):
	"""将其他地址设为非主地址"""
	sql = """
        UPDATE dev_device_address 
        SET is_primary = 0 
        WHERE device_type = %s AND device_id = %s AND address_type != %s
    """
	db.update(sql, (device_type, device_id, exclude_address_type))
